package com.rainng.coursesystem.repository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

@Repository
public class PostRes {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 根据课程ID获取帖子列表
    public List<Map<String, Object>> getPostsByCourseId(int courseId) {
        String sql = "SELECT post_id, title, content, image_url, created_at, studentid " +
                "FROM rc_post WHERE courseid = ?";
        return jdbcTemplate.queryForList(sql, courseId);
    }

    // 获取单个帖子详情
    public Map<String, Object> getPostById(int postId) {
        String sql = "SELECT post_id, title, content, image_url, created_at, studentid " +
                "FROM rc_post WHERE post_id = ?";
        return jdbcTemplate.queryForMap(sql, postId);
    }

    // 点赞帖子（记录userId）
    public int likePost(int userId, int postId) {
        String sql = "INSERT INTO rc_post_likes (user_id, post_id) VALUES (?, ?) ON DUPLICATE KEY UPDATE liked_at = CURRENT_TIMESTAMP";
        return jdbcTemplate.update(sql, userId, postId);
    }

    // 取消点赞帖子
    public int unlikePost(int userId, int postId) {
        String sql = "DELETE FROM rc_post_likes WHERE user_id = ? AND post_id = ?";
        return jdbcTemplate.update(sql, userId, postId);
    }

    // 检查用户是否已点赞帖子
    public boolean isPostLikedByUser(int userId, int postId) {
        String sql = "SELECT COUNT(*) FROM rc_post_likes WHERE user_id = ? AND post_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId, postId);
        return count != null && count > 0;
    }

    // 判断帖子是否存在
    public boolean isPostExist(int postId) {
        String sql = "SELECT COUNT(*) FROM rc_post WHERE post_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, postId);
        return count != null && count > 0;
    }

    public List<Map<String, Object>> searchByKeywordAndCourseId(String keyword, int courseId) {
        // 定义 SQL 查询语句，包含帖子信息、点赞状态、收藏状态和点赞数
        String sql = "SELECT p.post_id, p.title, p.content, p.image_url, p.created_at, p.studentid, " +
                "       IF(pl.user_id IS NOT NULL, TRUE, FALSE) AS isLiked, " +
                "       (SELECT COUNT(*) FROM rc_post_likes WHERE post_id = p.post_id) AS likeCount, " +
                "       IF(f.user_id IS NOT NULL, TRUE, FALSE) AS isFavorited " +
                "FROM rc_post p " +
                "LEFT JOIN rc_comment c ON p.post_id = c.post_id " +
                "LEFT JOIN rc_post_likes pl ON p.post_id = pl.post_id " +
                "LEFT JOIN rc_favorite f ON p.post_id = f.post_id " +
                "WHERE p.courseid = ? " +
                "AND (p.title LIKE ? OR p.content LIKE ? OR c.content LIKE ?)";

        // 拼接模糊搜索的关键字
        String likeKeyword = "%" + keyword + "%";

        // 使用 jdbcTemplate 执行查询并返回结果
        return jdbcTemplate.queryForList(sql, courseId, likeKeyword, likeKeyword, likeKeyword);
    }





    // 收藏帖子
    public int addFavorite(int userId, int postId) {
        String sql = "INSERT INTO rc_favorite (user_id, post_id) VALUES (?, ?)";
        return jdbcTemplate.update(sql, userId, postId);
    }

    // 取消收藏帖子
    public int removeFavorite(int userId, int postId) {
        String sql = "DELETE FROM rc_favorite WHERE user_id = ? AND post_id = ?";
        return jdbcTemplate.update(sql, userId, postId);
    }

    // 检查是否已收藏
    public boolean isFavorite(int userId, int postId) {
        String sql = "SELECT COUNT(*) FROM rc_favorite WHERE user_id = ? AND post_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId, postId);
        return count != null && count > 0;
    }

    // 获取用户收藏的所有帖子，并包含点赞和收藏状态
    public List<Map<String, Object>> getFavoritePostsWithStatus(int userId) {
        String sql = "SELECT p.post_id, p.title, p.content, p.image_url, p.created_at, p.studentid, " +
                "       IF(pl.user_id IS NOT NULL, TRUE, FALSE) AS isLiked, " +
                "       (SELECT COUNT(*) FROM rc_post_likes WHERE post_id = p.post_id) AS likeCount, " +
                "       TRUE AS isFavorited " +  // 因为是收藏的帖子，收藏状态肯定是 TRUE
                "FROM rc_post p " +
                "LEFT JOIN rc_post_likes pl ON p.post_id = pl.post_id AND pl.user_id = ? " +
                "JOIN rc_favorite f ON p.post_id = f.post_id " +
                "WHERE f.user_id = ?";

        return jdbcTemplate.queryForList(sql, userId, userId);
    }

    // 获取帖子与点赞、收藏状态，并且返回 studentid
    public List<Map<String, Object>> getPostsWithLikeAndFavoriteStatus(int courseId, int userId) {
        String sql = "SELECT p.post_id, p.title, p.content, p.image_url, p.created_at, p.studentid, " +
                "       IF(pl.user_id IS NOT NULL, TRUE, FALSE) AS isLiked, " +
                "       (SELECT COUNT(*) FROM rc_post_likes WHERE post_id = p.post_id) AS likeCount, " +
                "       IF(f.user_id IS NOT NULL, TRUE, FALSE) AS isFavorited " +
                "FROM rc_post p " +
                "LEFT JOIN rc_post_likes pl ON p.post_id = pl.post_id AND pl.user_id = ? " +
                "LEFT JOIN rc_favorite f ON p.post_id = f.post_id AND f.user_id = ? " +
                "WHERE p.courseid = ?";

        return jdbcTemplate.queryForList(sql, userId, userId, courseId);
    }
}
